package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import util.GetTime;

public class NoticeBoardDAO {
	
	public static void main(String[] args){

	}
	
	/**
	 * 페이지 리스트 추출 메소드
	 * */
	public Vector<NoticeBoard> getBoardListPage(int cono, long currentPage, long rowsPerPage){
		
		Vector<NoticeBoard> vector = new Vector<NoticeBoard>();
		//#*
		int rownum = (int)getTotalRows() + 1;//글번호_마지막번호~1
		int index = 0;	     //모든 행에 1번부터 끝행까지 번호를 임시로 준다
		long startindex = 0; //보여질 페이지의 첫행의 인덱스
		long endindex = 0;   //보여질 페이지의 마지막행의 인덱스
		startindex = (currentPage - 1) * rowsPerPage + 1;
		endindex = currentPage * rowsPerPage;
		//*#
		Connection conn = ConnectionManager.getConnection();
		/*String sql = "select n.* from(select notice.*,(@rownum:=@rownum+1) rownum " +
				"from (select noticeboard.*,(@rownum:=0) rnum  from noticeboard where cono=?) notice) " +
				"n order by rownum desc limit "+rowsPerPage*(currentPage-1)+","+rowsPerPage;*/
		String sql = "select * from noticeboard where cono=? order by nbno desc ";
		
		try {
			///////////////////////////////////////////////
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setLong(1, cono);
			ResultSet rs = pstmt.executeQuery();			
			
			while(rs.next()) {
				//#*
				index += 1;		//## 모든 행에 인덱스번호 부여 1~끝까지
				rownum -= 1;	
				if(index >= startindex && index <= endindex){	//##
					System.out.println("rownum = " + rownum); //*#
					NoticeBoard nb = new NoticeBoard();
					nb.setNbno(rs.getInt(1));
					nb.setCono(rs.getInt(2));
					nb.setMid(rs.getString(3));
					nb.setNbtitle(rs.getString(4));
					nb.setNbdate(rs.getString(5).substring(0,19));
					nb.setNbcontent(rs.getString(6));
					nb.setNbcounter(rs.getInt(7));
	//@@			nb.setRownum(rs.getInt(9));
					nb.setRownum(rownum);	//##
					vector.add(nb);
				}
			}
			rs.close();
			pstmt.close();
			///////////////////////////////////////////////
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(conn!=null) try { conn.close(); } catch(Exception e) {}
		}
		return vector;
	}
	
	/**
	 * NoticeBoard article updating method
	 * parameter NoticeBoard nb
	 * */
	public int update(NoticeBoard nb){
		String sql = "update noticeboard set " +
				"nbtitle = ?," +
				"nbcontent = ? " +
				"where nbno=?";
		Connection conn = null;
		int result = 0;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, nb.getNbtitle());
			pstmt.setString(2, nb.getNbcontent());
			pstmt.setInt(3, nb.getNbno());
						
			result = pstmt.executeUpdate();
			
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/**
	 * NoticeBoard article deleting method
	 * parameter NoticeBoard nb
	 * */
	public int delete(NoticeBoard nb){
		String sql = "delete from noticeboard where nbno=?";
		Connection conn = null;
		int result = 0;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, nb.getNbno());
			
			result = pstmt.executeUpdate();
			
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		return result;
	}
	
	public int delete(int cono){
		String sql = "delete from noticeboard where cono=?";
		Connection conn = null;
		int result = 0;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, cono);
			
			result = pstmt.executeUpdate();
			
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/**
	 * NoticeBoard article inserting method
	 * parameter NoticeBoard nb
	 * */
	public int insert(NoticeBoard nb){
//@@    String sql = "insert into noticeboard (cono,mid,nbtitle,nbdate,nbcontent,nbcounter) " +
		String sql = "insert into noticeboard (nbno,cono,mid,nbtitle,nbdate,nbcontent,nbcounter) " +
		//@@		"values (?,?,?,now(),?,?)";
			"values (nbseq.NEXTVAL,?,?,?,?,?,?)";
		Connection conn = null;
		int result = 0;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, nb.getCono());
			pstmt.setString(2, nb.getMid());
			pstmt.setString(3, nb.getNbtitle());
			pstmt.setTimestamp(4, GetTime.nowTime); //##
			/*@@pstmt.setString(4, nb.getNbcontent());
			pstmt.setInt(5, nb.getNbcounter());*/
			pstmt.setString(5, nb.getNbcontent());
			pstmt.setInt(6, nb.getNbcounter());
			
			result = pstmt.executeUpdate();
			
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		return result;
	}

	
	/**
	 * 조성일 
	 * 현재 테이블에 총 카운트 취득
	 * */
	public long getTotalRows(){
		NoticeBoard noticeboard = new NoticeBoard();
		long totalCount = 0;
		String sql = "select count(*) as totalcount from noticeboard";
		Connection conn = null;
		
		try{			
			
			conn = ConnectionManager.getConnection();
			
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			
			while(rs.next()){
				
				totalCount = rs.getLong(1);
								
			}
			
		} catch(Exception e){
			e.printStackTrace();
		} finally {
			try {				
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		
		return totalCount;
	}
	
	/**
	 * NoticeBoard getting article method
	 * parameter int nbno
	 * */
	public NoticeBoard getNoticeBoard(int nbno){
		NoticeBoard noticeboard = new NoticeBoard();
		String sql = "select nbno, cono, mid, nbtitle, nbdate, nbcontent, nbcounter " +
				"from noticeboard where nbno="+nbno;
		Connection conn = null;
		
		try{			
			conn = ConnectionManager.getConnection();
			conn.setAutoCommit(false);
			
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				noticeboard.setNbno(rs.getInt("nbno"));
				noticeboard.setCono(rs.getInt("cono"));
				noticeboard.setMid(rs.getString("mid"));
				noticeboard.setNbtitle(rs.getString("nbtitle"));
				noticeboard.setNbdate(rs.getString("nbdate"));
				noticeboard.setNbcontent(rs.getString("nbcontent"));
				noticeboard.setNbcounter(rs.getInt("nbcounter"));					
			}
			
			conn.commit();
		} catch(Exception e){
			e.printStackTrace();
		} finally {
			try {				
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		
		return noticeboard;
	}
	
	public void setCounter(int nbno){
		Connection conn = null;
		String sql = "update noticeboard set nbcounter=nbcounter+1 where nbno=?";
		int result = 0;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, nbno);
			result = pstmt.executeUpdate();
			
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 게시물 수 반환 메소드
	 * */
	public int getTotalRows(int cono){
		Connection conn = null;
		String sql = "select count(*) from noticeboard where cono="+cono;
		int totalRow = 0;
		try{
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				totalRow = rs.getInt(1);
			}
		} catch(Exception e){
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return totalRow;
	}
	
	/**
	 * Next & Preview
	 * */
	public int getNext(NoticeBoard nb){
		int nextNumber = 0;
		String sql = "select min(nbno) from (select nbno from noticeboard where cono=?) n where nbno>?";
		Connection conn = null;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, nb.getCono());
			pstmt.setInt(2, nb.getNbno());
			ResultSet rs = pstmt.executeQuery();
			
			int no = 0;
			while(rs.next()){
				no = rs.getInt(1);				
			}			 
			nextNumber = no!=0?no:nb.getNbno();
			
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		return nextNumber;
		
	}

	public int getPreview(NoticeBoard nb){
		int previewNumber = 0;
		String sql = "select max(nbno) from (select nbno from noticeboard where cono=?) n where nbno<?";
		Connection conn = null;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, nb.getCono());
			pstmt.setInt(2, nb.getNbno());
			ResultSet rs = pstmt.executeQuery();
			
			int no = 0;
			while(rs.next()){
				no = rs.getInt(1);				
			}			 
			previewNumber = no!=0?no:nb.getNbno();
			
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		return previewNumber;
		
	}	
	
	public String getRownum(int cono, int nbno){
		Connection conn = ConnectionManager.getConnection();
		String sql = "select n.* from(select notice.*,(@rownum:=@rownum+1) rownum " +
				"from (select noticeboard.*,(@rownum:=0) rnum  from noticeboard where cono=?) notice) " +
				"n where nbno=? order by rownum desc";
		String result = null;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, cono);
			pstmt.setInt(2, nbno);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				result = rs.getString(9);
			}			
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		return result;
	}			
	
	//최신 등록한 데이타 가져오기
	public Vector<NoticeBoard> getNewContent(int cono){
		Vector<NoticeBoard> vector = new Vector<NoticeBoard>();
		String sql = "select * from noticeboard where cono=? order by nbdate desc limit 4";
		Connection conn = null;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, cono);
			
			ResultSet rs = pstmt.executeQuery();	
			
			while(rs.next()){
				NoticeBoard noticeBoard = new NoticeBoard();
				noticeBoard.setNbno(rs.getInt(1));
				noticeBoard.setCono(rs.getInt(2));
				noticeBoard.setMid(rs.getString(3));
				noticeBoard.setNbtitle(rs.getString(4));
				String date = rs.getString(5);
				date = date.substring(0, 10);
				
				noticeBoard.setNbdate(date);
				
				noticeBoard.setNbcontent(rs.getString(6));
				noticeBoard.setNbcounter(rs.getInt(7));
				vector.add(noticeBoard);
			}			 
			
			
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		return vector;
		
	}
}
